package dao.Impl;

import dao.CustomerRepository;
import entity.Customer;
import utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CustomerRepositoryImpl implements CustomerRepository {

    /**
     * 从数据库中获取客户信息表
     */
    @Override
    public  ArrayList<Customer> getCustomerList(){
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from servicemanagementsystem.customer";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Customer customer = null;
        ArrayList<Customer> customerList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String Name = resultSet.getString(2);
                Integer age = resultSet.getInt(3);
                String job = resultSet.getString(4);
                String addr = resultSet.getString(5);
                String phone = resultSet.getString(6);
                String wechat = resultSet.getString(7);
                String mail = resultSet.getString(8);
                customer = new Customer(id, Name, age,job,addr,phone,wechat,mail);
                customerList.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return customerList;
    }
    /**
     * 添加客户信息
     */
    @Override
    public boolean addCustomer(Customer customer) {
        Connection connection = null;
        String sql = "insert into servicemanagementsystem.customer (cid, name, age, job,addr,phone,wechat,mail) " +
                "values (?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, customer.getCid());
            ps.setString(2, customer.getName());
            ps.setInt(3, customer.getAge());
            ps.setString(4, customer.getJob());
            ps.setString(5,customer.getAddr());
            ps.setString(6,customer.getPhone());
            ps.setString(7,customer.getWechat());
            ps.setString(8,customer.getMail());
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("error when add customer");
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据客户编号查找数据库返回客户信息对象
     */
    @Override
    public Customer findById(Integer customerId) {
        Connection connection = null;
        String sql = "select * from servicemanagementsystem.customer where cid=?";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Customer customer = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, customerId);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String Name = resultSet.getString(2);
                Integer age = resultSet.getInt(3);
                String job = resultSet.getString(4);
                String addr = resultSet.getString(5);
                String phone = resultSet.getString(6);
                String wechat = resultSet.getString(7);
                String mail = resultSet.getString(8);
                customer = new Customer(id, Name, age,job,addr,phone,wechat,mail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return customer;
    }
    /**
     * 修改客户信息
     */
    @Override
    public boolean updateCustomer(Customer customer) {
        Connection connection = null;
        String sql = "update servicemanagementsystem.customer set name=? , age=?, job=?, addr=? ,phone=?,wechat=?,mail=? " +
                "where cid=?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setString(1, customer.getName());
            ps.setInt(2, customer.getAge());
            ps.setString(3, customer.getJob());
            ps.setString(4,customer.getAddr());
            ps.setString(5,customer.getPhone());
            ps.setString(6,customer.getWechat());
            ps.setString(7,customer.getMail());
            ps.setInt(8, customer.getCid());
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据客户编号删除客户信息
     */
    @Override
    public boolean deleteCustomer(Integer customerId) {
        Connection connection = null;
        String sql = "delete from servicemanagementsystem.customer where cid = ?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, customerId);
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }


}
